/**
 * User: alesj
 * Date: Sep 23, 2003
 * Time: 10:39:13 AM
 */

package com.generalynx.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.Calendar;
import java.util.Date;

import com.generalynx.common.utils.DateData;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class CommonTools {

    public static final Log logger = LogFactory.getLog(CommonTools.class);
    //full db url
    private static MessageFormat c_mf = new MessageFormat(CommonSettings.m_sDBurlFormat);

    /**
     * uses message format to create full MSSql database url
     *
     * @param db_server
     * @param db_database
     * @param username
     * @param password
     * @return mySql database url
     */
    public static String createFullDBUrl(String db_server,
                                         String db_database,
                                         String username,
                                         String password) {
        return createFullDBUrl(db_server, db_database, username, password, c_mf);
    }

    public static String createFullDBUrl(String db_server,
                                         String db_database,
                                         String username,
                                         String password,
                                         MessageFormat mf) {
        Object[] args = {db_server, db_database, username, password};
        synchronized(mf) {
            return mf.format(args);
        }
    }

    /**
     * registers driver class given in settings preperties
     *
     * @throws ClassNotFoundException
     */
    public static void registerDBDriver()
            throws ClassNotFoundException {
        logger.info(
                "Connecting with " + CommonSettings.m_sDBDriver + " (" +
                CommonSettings.m_sDB_Server +
                ")");
        Class driverClass = Class.forName(CommonSettings.m_sDBDriver);
    }

    /**
     * checks if registred driver is ok
     *
     * @param bReturnException
     * @throws java.sql.SQLException
     */
    public static void checkDBDriver(boolean bReturnException) throws SQLException {
        try {
            logger.info("Connecting to default : " + CommonSettings.m_defaultlDBUrl);
            CommonTools.getDBConnection().close();
        } catch (SQLException ex) {
            logger.error(ex);
            logger.warn("Checking database driver failed.");
            if (bReturnException) {
                throw ex;
            }
        }
    }

    /**
     * @return default connection set in settings
     * @throws java.sql.SQLException
     * @deprecated use ConnectionPool.reserveConnection
     */
    public static Connection getDBConnection()
            throws SQLException {
        return getDBConnection(CommonSettings.m_defaultlDBUrl);
    }

    /**
     * @param dbURL
     * @return connection with appropriate driver to given db url
     * @throws java.sql.SQLException
     */
    public static Connection getDBConnection(String dbURL)
            throws SQLException {
        //MySql, MSSql
        return DriverManager.getConnection(dbURL);
        //ODBC, Oracle
        //    return DriverManager.getConnection(CommonSettings.m_sDBURL, CommonSettings.m_sDBUsername, CommonSettings.m_sDBPassword);
    }

    public static String getUpdateEditorValues(int nEditorID) {
        return getUpdateEditorValues(nEditorID, new java.util.Date(), null);
    }

    public static String getUpdateEditorValues(int nEditorID, java.util.Date editTimestamp) {
        return getUpdateEditorValues(nEditorID, editTimestamp, null);
    }

    public static String getUpdateEditorValues(int nEditorID, String sTableName) {
        return getUpdateEditorValues(nEditorID, new java.util.Date(), sTableName);
    }

    public static String getUpdateEditorValues(int nEditorID,
                                               java.util.Date editTimestamp,
                                               String sTableName) {
        StringBuffer b = new StringBuffer();

        if (sTableName != null) {
            b.append(sTableName);
            b.append(".");
        }
        b.append("Editor_ID = ");
        b.append(write(nEditorID));
        b.append(", ");
        if (sTableName != null) {
            b.append(sTableName);
            b.append(".");
        }
        b.append("Edit_Timestamp = ");
        b.append(write(editTimestamp));

        return b.toString();
    }

    //methods for writing to database

    public static String write(Object value) {
        if (value instanceof String)
            return write((String)value);
        if (value instanceof Integer)
            return write(((Integer)value).intValue());
        if (value instanceof Long)
            return write(((Long)value).longValue());
        if (value instanceof Boolean)
            return write(((Boolean)value).booleanValue());
        if (value instanceof Double)
            return write(((Double)value).doubleValue());
        if (value instanceof Float)
            return write(((Float)value).floatValue());
        if (value instanceof Date)
            return write((Date)value);
        if (value instanceof DateData)
            return writeDate((DateData)value);
        return (value != null ? write(value.toString()) : "NULL"); //changed 16.7.2004 by alesj
    }

    public static String write(String sValue) {
        return sValue != null
                ? new StringBuffer().append("'").append(sValue).append("'").toString() : "''";
    }

    public static String write(boolean bValue) {
        return bValue ? "1" : "0";
    }

    public static String write(int nValue, boolean isZero) {
        //    return String.valueOf(nValue);
        // FIXIT: potreba po NULL?
        return ((nValue == 0 && !isZero) ? "NULL" : String.valueOf(nValue));
    }

    public static String write(char sValue) {
        return write(new String("" + sValue));
    }

    public static String write(int nValue) {
        return write(nValue, false);
    }

    public static String write(double dValue) {
        return String.valueOf(dValue);
    }

    public static String write(java.util.Date timestamp) {
        return write(timestamp, true, true);
    }

    public static String write(java.util.Date timestamp, boolean bDate, boolean bTime) {
        if (!(bDate || bTime)) {
            throw new IllegalArgumentException("bDate and bTime cannot be both false.");
        }

        if (timestamp == null) {
            return "NULL";
        }

        Calendar cal = Calendar.getInstance();
        cal.setTime(timestamp);

        StringBuffer sbuf = new StringBuffer(26);
        //    sbuf.append("{");
        //    sbuf.append(bDate && bTime ? "ts" : (bDate ? "d" : "t"));
        sbuf.append("'");
        if (bDate) {
            sbuf.append(cal.get(Calendar.YEAR));
            sbuf.append("-");
            sbuf.append(cal.get(Calendar.MONTH) - Calendar.JANUARY + 1);
            sbuf.append("-");
            sbuf.append(cal.get(Calendar.DAY_OF_MONTH));
            if (bTime) {
                sbuf.append(" ");
            }
        }
        if (bTime) {
            sbuf.append(cal.get(Calendar.HOUR_OF_DAY));
            sbuf.append(":");
            sbuf.append(cal.get(Calendar.MINUTE));
            sbuf.append(":");
            sbuf.append(cal.get(Calendar.SECOND));
        }
        sbuf.append("'");
        //    sbuf.append("'}");

        return sbuf.toString();
    }

    public static String write(DateData value) {
        return writeDate(value);
    }

    public static String writeDate(DateData value) {
        return writeDate(value, "NULL");
    }

    public static String writeDate(DateData value, String sNullValue) {
        return writeDate(value, sNullValue, 0, 0);
    }

    public static String writeDate(DateData value, int hour, int minute) {
        return writeDate(value, "NULL", hour, minute);
    }

    public static String writeDate(DateData value, String sNullValue, int hour, int minute) {
        return writeDate(value, sNullValue, hour, minute, true);
    }

    public static String writeDate(DateData value, String sNullValue, int hour, int minute, boolean quote) {
        if ((value == null) || !value.isValid()) {
            return sNullValue;
        } else {
            int nYear = value.getYear();
            int nMonth = value.getMonth();
            int nDay = value.getDay();

            StringBuffer b = new StringBuffer();

            if (quote) {
                b.append("'");
            }
            b.append(nYear);
            b.append("-");
            if (nMonth < 10) {
                b.append("0");
            }
            b.append(nMonth);
            b.append("-");
            if (nDay < 10) {
                b.append("0");
            }
            b.append(nDay);

            if (hour != 0 || minute != 0) {
                b.append(" ");
                b.append(hour);
                b.append(":");
                b.append(minute);
            }
            if (quote) {
                b.append("'");
            }

            return b.toString();
        }
    }

    //TODO:Popravi v bolj uporabno verzijo
    public static String writeDate(int year, int month, int day, int hour, int minute) {
        StringBuffer b = new StringBuffer();
        if (day < 10) b.append("0");
        b.append(day);
        b.append(".");
        if (month < 10) b.append("0");
        b.append(month);
        b.append(".");
        if (year < 10) b.append("0");
        b.append(year);
        b.append(" ");
        if (hour < 10) b.append("0");
        b.append(hour);
        b.append(":");
        if (minute < 10) b.append("0");
        b.append(minute);

        return b.toString();
    }

    public static String writeDate(DateData value, boolean slo) {
        return writeDate(value, "NULL", slo);
    }

    public static String writeDate(DateData value, String sNullValue, boolean slo) {
        return writeDate(value, sNullValue, slo, true);
    }

    public static String writeDate(DateData value, String sNullValue, boolean slo, boolean quote) {
        if (!slo) {
            return writeDate(value, sNullValue, 0, 0);
        } else if ((value == null) || !value.isValid()) {
            return sNullValue;
        } else {
            int nYear = value.getYear();
            int nMonth = value.getMonth();
            int nDay = value.getDay();

            StringBuffer b = new StringBuffer();

            if (quote) {
                b.append("'");
            }
            if (nDay < 10) {
                b.append("0");
            }
            b.append(nDay);
            b.append(".");
            if (nMonth < 10) {
                b.append("0");
            }
            b.append(nMonth);
            b.append(".");
            b.append(nYear);
            if (quote) {
                b.append("'");
            }

            return b.toString();
        }
    }

    public static String toProcedure(String procedure) {
        return toProcedure(procedure, 0);
    }

    public static String toProcedure(String procedure, int args) {
        StringBuffer buffer = new StringBuffer(procedure);

        buffer.insert(0, "{call ");
        buffer.append("(");
        boolean first = true;
        for (int i = 0; i < args; i++) {
            if (!first) {
                buffer.append(",");
            }
            buffer.append("?");
            first = false;
        }
        buffer.append(")}");

        return buffer.toString();
    }
}
